﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Diagnostics;
using System.Text.RegularExpressions;
using System.Configuration;


namespace etcd
{
    class DataOperate
    {
        //add by fuxiding 20121106
        private static string strLinkSQL = ConfigurationManager.ConnectionStrings["Local_DB"].ConnectionString.ToString();
        //end add

        private static SqlConnection sqlConn = new SqlConnection(strLinkSQL);
        private static SqlCommand sqlComm = new SqlCommand();
        private static SqlDataAdapter sqlDa = new SqlDataAdapter();

        //数据库打开。成功返回true，不成功返回false。
        public bool databaseOpen()
        {
            bool result = false;

            try
            {
                //判断数据连接连接是否已经打开，已经打开的，关闭重开
                if (sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                }
                sqlConn.Open();

                //判断打开是否成功
                if (sqlConn.State == ConnectionState.Open)
                {
                    result = true;
                }
                else
                {
                    result = false;
                }
            }
            catch (Exception ex)
            {
                result = false;
            }

            return result;
        }

        //数据库关闭。成功返回true，不成功返回false。
        public bool databaseClose()
        {
            bool result = false;
            try
            {
                //判断连接是否关闭
                if (sqlConn.State != ConnectionState.Closed)
                {
                    sqlConn.Close();

                    if (sqlConn.State == ConnectionState.Closed)
                    {

                        result = true;
                    }
                    else
                    {
                        result = false;
                    }
                }
                else
                {
                    result = true;
                }
            }
            catch (Exception ex)
            {
                result = false;
            }


            return result;
        }

        //用户登录验证
        public int GetLoginAuthority(string loginName, string loginPassword)
        {
            int authorityFlag = 0;
            DataSet ds = new DataSet();//

            if (loginName.Trim().Length > 0 && loginPassword.Trim().Length > 0)
            {
                if (sqlConn.State != ConnectionState.Open)
                {
                    databaseOpen();
                }

                //modify by jacky 20130422 增加查询条件 authority <> '-1'
                string strQuerySQL = "SELECT * FROM userTB WHERE userName = '" + loginName + "' AND password = '" + loginPassword + "' AND authority <> '-1'";
                //end modify
                
                sqlDa.SelectCommand = new SqlCommand(strQuerySQL, sqlConn);
                sqlDa.Fill(ds, "userTempTB");

                if (ds.Tables[0].Rows.Count == 1)
                {
                    authorityFlag = Convert.ToInt32(ds.Tables["userTempTB"].Rows[0]["authority"]);
                }
                else
                {
                    authorityFlag = 0;
                }

                ds.Clear();
                return authorityFlag;
            }
            else
            {
                return 0;
            }
        }

        //用户名检验函数
        public bool CheckUserName(string userName)
        {
            bool result = false;
            if (sqlConn.State != ConnectionState.Open)
            {
                databaseOpen();
            }
            string strQuerySQL = "SELECT userName FROM userTB WHERE userName = '" + userName.Trim() + "' AND authority <> '-1'";

            sqlComm = new SqlCommand(strQuerySQL, sqlConn);
            SqlDataReader sqlRead = sqlComm.ExecuteReader();
            if (true == sqlRead.Read())
            {
                result = true;
            }
            else
            {
                result = false;
            }
            sqlRead.Close();
            sqlRead.Dispose();
            return result;
        }

        //用户列表
        public DataTable GetUserInfo(string strUserName, int authority)
        {
            DataSet ds = new DataSet();
            if (ds != null)
            {
                ds.Clear();
            }

            if (sqlConn.State != ConnectionState.Open)
            {
                databaseOpen();
            }

            string strQuerySQL = "SELECT userNo, userName, authority FROM userTB WHERE (userName = '" + strUserName +
                                 "' AND authority <> '-1') OR (authority > " + authority + ") ORDER BY authority , userNo";

            sqlDa.SelectCommand = new SqlCommand(strQuerySQL, sqlConn);
            sqlDa.Fill(ds, "userInfoTB");
            if (ds.Tables["userInfoTB"].Rows.Count > 0)
            {
                return ds.Tables["userInfoTB"];
            }
            else
            {
                return null;
            }
        }


        //用户编号 
        public int CreateUserNo()
        {
            int newUserNo;
            DataSet ds = new DataSet();
            if (ds != null)
            {
                ds.Clear();
            }

            if (sqlConn.State != ConnectionState.Open)
            {
                databaseOpen();
            }

            string strQuerySQL = "SELECT MAX(userNo) FROM userTB";

            sqlComm = new SqlCommand(strQuerySQL, sqlConn);
            SqlDataReader userNoReader = sqlComm.ExecuteReader();

            if (true == userNoReader.Read())
            {
                newUserNo = Convert.ToInt32(userNoReader[0]) + 1;
            }
            else
            {
                newUserNo = -1;
            }

            userNoReader.Close();
            userNoReader.Dispose();
            return newUserNo;

        }



        //用户添加
        public bool AddUser(string userName, string passWD, int authority)
        {
            bool result = false;
            if (userName.Trim().Length < 1 && passWD.Trim().Length < 6 && (authority < 0 || authority > 3))
            {
                return false;
            }

            if (sqlConn.State != ConnectionState.Open)
            {
                databaseOpen();
            }

            string strQuerySQL = "SELECT * FROM userTB WHERE userName = '" + userName + "' AND authority <> '-1'";

            sqlComm = new SqlCommand(strQuerySQL, sqlConn);

            SqlDataReader userReader = sqlComm.ExecuteReader();

            if (true == userReader.Read())
            {
                return false;
            }

            userReader.Close();
            userReader.Dispose();

            sqlComm.CommandText = "INSERT INTO userTB (userName, password, authority) VALUES ( '" +
                                   userName + "', '" + passWD + "'," + authority + ")";

            if (1 == sqlComm.ExecuteNonQuery())
            {
                result = true;
            }
            else
            {

                result = false;
            }
            return result;
        }

        //用户删除
        public bool DeleteUser(string userName)
        {
            bool result = false;
            if (userName.Trim().Length < 1)
            {
                result = false;
            }

            if (sqlConn.State != ConnectionState.Open)
            {
                databaseOpen();
            }

            string strQuerySQL = "UPDATE userTB SET authority = (-1) WHERE (userName = '" + userName + "' AND authority <> '-1')";
            sqlComm = new SqlCommand(strQuerySQL, sqlConn);
            if (1 == sqlComm.ExecuteNonQuery())
            {
                result = true;
            }
            else
            {
                result = false;
            }


            return result;
        }

        //密码重置
        public bool ResetPassword(string userName, string passWD)
        {
            bool result = false;
            if (userName.Trim().Length < 1 && passWD.Trim().Length < 6)
            {
                result = false;
            }

            if (sqlConn.State != ConnectionState.Open)
            {
                databaseOpen();
            }

            string strQuerySQL = "UPDATE userTB SET password = '" + passWD + "' WHERE  userName =  '" + userName + "' AND authority <> '-1'";
            sqlComm = new SqlCommand(strQuerySQL, sqlConn);
            if (1 == sqlComm.ExecuteNonQuery())
            {
                result = true;
            }
            else
            {
                result = false;
            }


            return result;
        }

        //修改密码
        public bool ModifyPassword(string userName, string oldPassword, string newPassword)
        {
            bool result = false;
            if (userName.Trim().Length < 1 && oldPassword.Trim().Length < 6 && newPassword.Trim().Length < 6)
            {
                result = false;
            }

            if (sqlConn.State != ConnectionState.Open)
            {
                databaseOpen();
            }

            string strQuerySQL = "UPDATE userTB SET password = '" + newPassword + "' WHERE  userName =  '" +
                                 userName + "' AND password = '" + oldPassword + "' AND authority <> '-1'";

            sqlComm = new SqlCommand(strQuerySQL, sqlConn);
            if (1 == sqlComm.ExecuteNonQuery())
            {
                result = true;
            }
            else
            {
                result = false;
            }

            return result;
        }


        //修改权限
        public bool ModifyUserAuthority(string userName, int authority)
        {
            bool result = false;
            if (userName.Trim().Length < 1 || authority < 1 || authority > 3)
            {
                result = false;
            }

            if (sqlConn.State != ConnectionState.Open)
            {
                databaseOpen();
            }

            string strQuerySQL = "UPDATE userTB SET authority = '" + authority + "' WHERE  userName =  '" + userName + "' AND authority <> '-1'";

            sqlComm = new SqlCommand(strQuerySQL, sqlConn);
            if (1 == sqlComm.ExecuteNonQuery())
            {
                result = true;
            }
            else
            {
                result = false;
            }

            return result;
        }

        //按搜索条件组合SQL语句的条件部分
        private string CaseCondition(string patientName, string dep, string dateFrom, string dateTo)
        {
            int nameLength = patientName.Trim().Length;
            int deptLength = dep.Trim().Length;
            int dateFromLength = dateFrom.Trim().Length;
            int dateToLength = dateTo.Trim().Length;

            string strCondition = "";
            if (nameLength > 0)                                 //有姓名
            {
                if (deptLength > 0)                             //有姓名，有科室
                {
                    if (dateFromLength > 0)                      //有姓名，有科室，有时间范围
                    {
                        if (dateToLength < 6)
                        {
                            dateTo = DateTime.Now.Date.ToString();
                        }
                        strCondition = " AND examinationTB.examinationDate >= '" + Convert.ToDateTime(dateFrom).ToString("yyyy-MM-dd 00:00:00") +
                                        "' AND examinationTB.examinationDate <='" + Convert.ToDateTime(dateTo).ToString("yyyy-MM-dd 23:59:59") +
                                        "' AND examinationTB.patientNo = (SELECT top 1 patientNo FROM patientTB WHERE  patientName = '" + patientName + "'AND dept = '" + dep + "')"; //正式使用时删 top 1 
                    }
                    else                                        //有姓名，有科室，没有时间范围
                    {
                        strCondition = " AND examinationTB.patientNo = (SELECT top 1 patientNo  FROM patientTB WHERE  patientName = '" + //正式使用时删 top 1
                                    patientName + "' AND dept = '" + dep + "') ";
                    }
                }
                else                                            //有姓名，无科室
                {
                    if (dateFromLength > 0)                     //有姓名，无科室，有时间范围
                    {
                        if (dateToLength < 6)
                        {
                            dateTo = DateTime.Now.Date.ToString();
                        }
                        strCondition = " AND examinationTB.patientNo = (SELECT top 1  patientNo FROM patientTB WHERE  patientName = '" +    //正式使用时删  top 1
                                        patientName + "' AND examinationTB.examinationDate >= '" + Convert.ToDateTime(dateFrom).ToString("yyyy-MM-dd 00:00:00") +
                                        "' AND examinationTB.examinationDate <='" + Convert.ToDateTime(dateTo).ToString("yyyy-MM-dd 23:59:59") + "')";
                    }
                    else                                         //有姓名，无科室，没有时间范围
                    {
                        strCondition = " AND examinationTB.patientNo = (SELECT top 1 patientNo FROM patientTB WHERE  patientName = '" + patientName + "')";//正式使用时删 top 1
                    }
                }
            }
            else                                                 ///无姓名
            {
                if (deptLength > 0)                              //无姓名，有科室
                {
                    if (dateFromLength > 0)                      //无姓名，有科室，有时间范围
                    {
                        if (dateToLength < 6)
                        {
                            dateTo = DateTime.Now.Date.ToString();
                        }
                        strCondition = " AND patientTB.dept = '" + dep +
                                "' AND examinationTB.examinationDate >= '" + Convert.ToDateTime(dateFrom).ToString("yyyy-MM-dd 00:00:00") +
                                "' AND examinationTB.examinationDate <='" + Convert.ToDateTime(dateTo).ToString("yyyy-MM-dd 23:59:59") + "'";
                    }
                    else                                         //无姓名，有科室，没有时间范围
                    {
                        strCondition = "AND patientTB.dept = '" + dep + "' ";
                    }
                }
                else                                             //无姓名，无科室
                {
                    if (dateFromLength > 0)                       //无姓名，无科室，有时间范围
                    {
                        if (dateToLength < 6)
                        {
                            dateTo = DateTime.Now.Date.ToString();
                        }
                        strCondition = " AND examinationTB.examinationDate BETWEEN '" + Convert.ToDateTime(dateFrom).ToString("yyyy-MM-dd 00:00:00") +
                                        "' AND '" + Convert.ToDateTime(dateTo).ToString("yyyy-MM-dd 23:59:59") + "'";
                    }

                    //无姓名，无科室，没有时间范围，即全部病例
                }
            }

            return strCondition;
        }

        //病例总数
        public int CaseCount(string patientName, string dep, string dateFrom, string dateTo, string status)
        {
            if (status.Trim().Length < 1)
            {
                return -1;
            }

            DataSet ds = new DataSet();//ds
            if (ds != null)
            {
                ds.Clear();
            }

            if (sqlConn.State != ConnectionState.Open)
            {
                databaseOpen();
            }
            string strCondition = CaseCondition(patientName, dep, dateFrom, dateTo);
            string strCaseCountSQL = "SELECT COUNT(examinationTB.examinationNo) AS rowsCount FROM examinationTB " +
                                     "INNER JOIN patientTB ON examinationTB.patientNo = patientTB.patientNo " +
                                     "WHERE (status = '" + status + "')" + strCondition;

            sqlDa.SelectCommand = new SqlCommand(strCaseCountSQL, sqlConn);
            sqlDa.Fill(ds, "caseCountTempTB");

            return Convert.ToInt32(ds.Tables["caseCountTempTB"].Rows[0]["rowsCount"]);
        }

        //病例列表
        /// <summary>
        /// 获取病例列表
        /// </summary>
        /// <param name="patientName"></param>
        /// <param name="dep"></param>
        /// <param name="dateFrom"></param>
        /// <param name="dateTo"></param>
        /// <param name="status"></param>
        /// <param name="pageSize"></param>
        /// <param name="currentpage"></param>
        /// <returns></returns>
        public DataTable GetPatientInfo(string patientName, string dep, string dateFrom, string dateTo, string status, int pageSize, int currentpage)
        {
            DataSet ds = new DataSet();
            if (ds != null)
            {
                ds.Clear();
            }


            if (sqlConn.State != ConnectionState.Open)
            {
                databaseOpen();
            }

            string strCondition = CaseCondition(patientName, dep, dateFrom, dateTo);

            //----------------------- add by wzt 20130426 搜索病例改成固定15条记录
            string strQuerySQL = "SELECT TOP " + "15" + " patientTB.patientNo, examinationTB.examinationNo,patientTB.patientName, " +
                     "patientTB.gender,patientTB.age, examinationTB.examinationDate,patientTB.inpatientNo," +
                     "patientTB.outpatientNo,patientTB.dept,examinationTB.stiffness,patientTB.telephone,examinationTB.iqr,examinationTB.successRate,examinationTB.flai,examinationTB.flaiIQR " +
                     "FROM examinationTB INNER JOIN patientTB " +
                     "ON examinationTB.patientNo = patientTB.patientNo " +
                     "WHERE (examinationTB.status = '" + status + "'" + strCondition + ") " +
                     "AND examinationTB.examinationNo NOT IN " +
                     "(SELECT TOP " + 15 * (currentpage - 1) + " examinationNo FROM examinationTB " +
                     "WHERE (examinationTB.status = '" + status + "'" + strCondition + ") ORDER BY examinationTB.examinationNo desc)" +
                     "ORDER BY examinationTB.examinationNo desc";
            //------------------------ end add

            sqlDa.SelectCommand = new SqlCommand(strQuerySQL, sqlConn);
            sqlDa.Fill(ds, "patientInfoTB");


            return ds.Tables["patientInfoTB"];
        }

        //病例列表
        /// <summary>
        /// 获取病例列表
        /// </summary>
        /// <param name="patientName"></param>
        /// <param name="dep"></param>
        /// <param name="dateFrom"></param>
        /// <param name="dateTo"></param>
        /// <param name="status"></param>
        /// <param name="pageSize"></param>
        /// <param name="currentpage"></param>
        /// <returns></returns>
        public DataTable GetPatientInfo(string patientName, string dep, string dateFrom, string dateTo, string status)
        {
            DataSet ds = new DataSet();
            if (ds != null)
            {
                ds.Clear();
            }


            if (sqlConn.State != ConnectionState.Open)
            {
                databaseOpen();
            }

            string strCondition = CaseCondition(patientName, dep, dateFrom, dateTo);

            //----------------------- add by wzt 20130426 搜索病例改成固定15条记录
            string strQuerySQL = "SELECT examinationTB.examinationNo,patientTB.patientName, " +
                     "patientTB.gender,patientTB.age, examinationTB.examinationDate," +
                     "patientTB.outpatientNo,patientTB.dept,examinationTB.stiffness,patientTB.telephone " +
                     "FROM examinationTB INNER JOIN patientTB " +
                     "ON examinationTB.patientNo = patientTB.patientNo " +
                     "WHERE (examinationTB.status = '" + status + "'" + strCondition + ") " +
                     "ORDER BY examinationTB.examinationNo desc";
            //------------------------ end add

            sqlDa.SelectCommand = new SqlCommand(strQuerySQL, sqlConn);
            sqlDa.Fill(ds, "patientInfoTB");


            return ds.Tables["patientInfoTB"];
        }

        //病例详细数据
        public DataTable GetStiffnessInfo(string strExaminationNo)
        {
            DataSet ds = new DataSet();//ds
            if (ds != null)
            {
                ds.Clear();
            }

            if (sqlConn.State != ConnectionState.Open)
            {
                databaseOpen();
            }

            string strQuerySQL = "SELECT examinationNo, detectNo, stiffness " +
                           "FROM detectTB WHERE (examinationNo = '" + strExaminationNo + "')";

            sqlDa.SelectCommand = new SqlCommand(strQuerySQL, sqlConn);
            sqlDa.Fill(ds, "stiffnessInfoTB");
            return ds.Tables["stiffnessInfoTB"];
        }
        
        /*add by jackyfu*/
        //每组检测的详细数据
        public DataTable GetDetectInfo(string strExaminationNo)
        {
            DataSet ds = new DataSet();//ds
            if (ds != null)
            {
                ds.Clear();
            }

            if (sqlConn.State != ConnectionState.Open)
            {
                databaseOpen();
            }

            string strQuerySQL = "SELECT * " +
                           "FROM detectTB WHERE (examinationNo = '" + strExaminationNo + "')";

            sqlDa.SelectCommand = new SqlCommand(strQuerySQL, sqlConn);
            sqlDa.Fill(ds, "detectInfoTB");
            return ds.Tables["detectInfoTB"];
        }
        /*end add*/

        //病例搜索
        public DataTable CasesFind(string userName, string dept, DateTime startDate, DateTime endDate)
        {
            DataSet ds = new DataSet();//ds
            string strQuerySQL = "SELECT patientTB.patientNo, patientTB.patientName, patientTB.gender, patientTB.age, " +
                                "patientTB.doctorNo,patientTB.medicalService, patientTB.telephone, examinationTB.examinationNo, " +
                                "examinationTB.examinationDate, examinationTB.stiffness, examinationTB.status" +
                                "FROM detectTB INNER JOIN examinationTB ON " +
                                "detectTB.examinationNo = examinationTB.examinationNo INNER JOIN" +
                                "patientTB ON examinationTB.patientNo = patientTB.patientNo ";
            if (ds != null)
            {
                ds.Clear();
            }

            if (sqlConn.State != ConnectionState.Open)
            {
                databaseOpen();
            }
            int nameLength = userName.Trim().Length;
            int deptLength = dept.Trim().Length;


            if (nameLength > 0)                                 //有姓名
            {
                if (deptLength > 0)                             //有姓名，有科室
                {
                    if (startDate != null)                      //有姓名，有科室，有时间范围
                    {
                        strQuerySQL += "WHERE patientTB.patientName = '" + userName +
                                "' AND patientTB.medicalService = '" + dept +
                                "' AND examinationTB.examinationDate BETWEEN '" + startDate.ToString("yyyy-MM-dd 00:00:00") +
                                "' AND '" + endDate.Date.ToString("yyyy-MM-dd 23:59:59") + "'";
                    }
                    else                                        //有姓名，有科室，没有时间范围
                    {
                        strQuerySQL += "WHERE patientTB.patientName = '" + userName +
                                "' AND patientTB.medicalService = '" + dept + "' ";
                    }
                }
                else                                            //有姓名，无科室
                {
                    if (startDate != null)                      //有姓名，无科室，有时间范围
                    {
                        strQuerySQL += "WHERE patientTB.patientName = '" + userName +
                                "' AND examinationTB.examinationDate BETWEEN '" + startDate.ToString("yyyy-MM-dd 00:00:00") +
                                "' AND '" + endDate.Date.ToString("yyyy-MM-dd 23:59:59") + "'";
                    }
                    else                                         //有姓名，无科室，没有时间范围
                    {
                        strQuerySQL += "WHERE patientTB.patientName = '" + userName + "'";
                    }
                }
            }
            else                                                 ///无姓名
            {
                if (deptLength > 0)                              //无姓名，有科室
                {
                    if (startDate != null)                       //无姓名，有科室，有时间范围
                    {
                        strQuerySQL += "WHERE patientTB.medicalService = '" + dept +
                                "' AND examinationTB.examinationDate BETWEEN '" + startDate.ToString("yyyy-MM-dd 00:00:00") +
                                "' AND '" + endDate.Date.ToString("yyyy-MM-dd 23:59:59") + "'";
                    }
                    else                                         //无姓名，有科室，没有时间范围
                    {
                        strQuerySQL += "WHERE patientTB.medicalService = '" + dept + "' ";
                    }
                }
                else                                             //无姓名，无科室
                {
                    if (startDate != null)                       //无姓名，无科室，有时间范围
                    {
                        strQuerySQL += "WHERE examinationTB.examinationDate BETWEEN '" + startDate.ToString("yyyy-MM-dd 00:00:00") +
                            "' AND '" + endDate.Date.ToString("yyyy-MM-dd 23:59:59") + "'";
                    }

                    //无姓名，无科室，没有时间范围，即全部病例
                }
            }


            sqlDa.SelectCommand = new SqlCommand(strQuerySQL, sqlConn);
            sqlDa.Fill(ds, "casesFindTempDT");
            return ds.Tables["casesFindTempDT"];
        }

        //新建病人
        public int AddPatient(string patientName, string patientGender, string patientAge,/*DateTime cDate,*/ string dep, string outpatientNo,
                              string inpatientNo, string telephone, string userName, string casesSource,string height,string weight)
        {
            int patientNo = 0;
            DataSet ds = new DataSet();//ds
            if (ds != null)
            {
                ds.Clear();
            }

            if (sqlConn.State != ConnectionState.Open)
            {
                databaseOpen();
            }

            string strPatientInsert = "INSERT INTO patientTB (patientName, age, gender, creatDate, dept, outpatientNo, " +
                                "inpatientNo,telephone, userName, casesSource, height, weight )VALUES ('" + patientName + "','" + patientAge +
                                "','" + patientGender + "','" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "','" + dep + "','" +
                                outpatientNo + "','" + inpatientNo + "','" + telephone + "','" + userName + "','" + casesSource + "','" + height + "','" + weight + "')";


            //向患者表中插入新建的患者
            sqlComm = new SqlCommand(strPatientInsert, sqlConn);
            if (1 == sqlComm.ExecuteNonQuery())
            {
                sqlComm.CommandText = "select MAX(patientNo) AS patientNo from patientTB";
                SqlDataReader sqlRead = sqlComm.ExecuteReader();
                if (true == sqlRead.Read())
                {
                    patientNo = Convert.ToInt32(sqlRead[0]);
                }
                sqlRead.Close();
                sqlRead.Dispose();
            }
            else
            {
                patientNo = -1;
            }


            return patientNo;
        }

        //获取科室列表
        public DataTable GetDepInfo()
        {
            DataSet ds = new DataSet();
            if (ds != null)
            {
                ds.Clear();
            }

            if (sqlConn.State != ConnectionState.Open)
            {
                databaseOpen();
            }

            string strDeptGroupSQL = "SELECT dept AS dep FROM patientTB GROUP BY dept";
            sqlDa.SelectCommand = new SqlCommand(strDeptGroupSQL, sqlConn);
            sqlDa.Fill(ds, "depTB");
            return ds.Tables["depTB"];
        }

        //生成检查编号
        public string CreateExaminationNo()
        {
            if (sqlConn.State != ConnectionState.Open)
            {
                databaseOpen();
            }
            DateTime nowDate = DateTime.Now;
            string strExaminationNo = nowDate.ToString("yyyyMMdd");

            string strQuerySQL = "select top 1 examinationNo,examinationDate from examinationTB order by examinationDate desc";

            sqlComm = new SqlCommand(strQuerySQL, sqlConn);
            SqlDataReader sqlRead = sqlComm.ExecuteReader();
            int serialNo = 0;
            if (true == sqlRead.Read())
            {
                DateTime typeDate = Convert.ToDateTime(sqlRead[1]).Date;
                if (DateTime.Compare(typeDate, nowDate.Date) == 0)
                {
                    serialNo = Convert.ToInt32(sqlRead[0].ToString().Substring(8, 4)) + 1;
                    strExaminationNo += serialNo.ToString().PadLeft(4, '0');
                }
                else if ((DateTime.Compare(typeDate, nowDate.Date) < 0))
                {
                    strExaminationNo += "0001";
                }
            }
            else
            {
                strExaminationNo += "0001";
            }
            sqlRead.Close();
            sqlRead.Dispose();


            bool result = false;
            do
            {
                strQuerySQL = "select examinationNo from examinationTB where examinationNo = '" + strExaminationNo + "'";
                sqlComm = new SqlCommand(strQuerySQL, sqlConn);
                sqlRead = sqlComm.ExecuteReader();
                result = sqlRead.Read();
                if (true == result)
                {
                    serialNo = Convert.ToInt32(sqlRead[0].ToString().Substring(8, 4)) + 1;
                    strExaminationNo = nowDate.ToString("yyyyMMdd") + serialNo.ToString().PadLeft(4, '0');
                }
                sqlRead.Close();
            } while (result);
            
            sqlRead.Dispose();

            //add by jacky 20130420
            return strExaminationNo;
            //end add
            
            ////delete by jacky 20130420
            //string strExaminationInsert = "INSERT INTO examinationTB (examinationNo,examinationDate)VALUES( '" + strExaminationNo + "','" + nowDate + "')";
            ////向检查表中插入新建的检查编号
            //sqlComm = new SqlCommand(strExaminationInsert, sqlConn);
            //if (1 == sqlComm.ExecuteNonQuery())
            //{
            //    return strExaminationNo;
            //}
            //else
            //{
            //    return null;
            //}
            ////end delete
        }

        //保存检查数据
        public bool SaveExaminationInfo(DataTable dtExamination, DataTable dtStiffnessInfo)
        {
            /*
             * DataTable: dtExamination
            
                字段	        类型	说明
                examinationNo	string	检测编号
                patientNo	    string	患者编号
                stiffness	    string	压力值（十次平均后的）
                IQR	            string	IQR
                successRate	    string	成功率 
                aMode	        image	A模式图片（最后结果）
                tmMode	        image	Tm模式图片（最后结果）
                elasticGraph	Image	弹性图（最后结果） 
                status	        string	诊断意见可否编辑
                detectorType	string	探头类型
                submitDoctor	string	送检医生
                diagnostician	string	诊断医生
                secondOpinion	string	诊断意见
             * 
             * 
             * 
             * DataTable: dtStiffnessInfo
               字段	            类型	说明
               examinationNo	string	检测编号
               detectNo	        string	序号
               stiffness	    string	压力值
               aMode	        image	A模式图片
               tmMode	        image	Tm模式图片
               elasticGraph	    Image	弹性图
               pressure         string  压力值
             */

            SqlParameter parameter;
            bool result = false;

            if (sqlConn.State != ConnectionState.Open)
            {
                databaseOpen();
            }

            //向检查表中新建该患者的检查    


            if (dtExamination.Rows.Count > 0)
            {
                ////delete by jacky  20130420
                ////更新检查表(已经有检查编号)
                //string strUpdateSQL = @"UPDATE examinationTB SET " +
                //                        "patientNo = " + dtExamination.Rows[0][1] +
                //                        ",examinationDate = '" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") +
                //                        "',stiffness = '" + dtExamination.Rows[0][2] +
                //                        "',iqr = '" + dtExamination.Rows[0][3] +
                //                        "',successRate = '" + dtExamination.Rows[0][4] +
                //                        "',aMode = " + "@aMode" +
                //                        ",tmMode = @tmMode" +
                //                        ",elasticGraph = @elasticGraph" +
                //                        ",status = '" + dtExamination.Rows[0][8] +
                //                        "',detectorType =  '" + dtExamination.Rows[0][9] +
                //                        "',submitDoctor =  '" + dtExamination.Rows[0][10] +
                //                        "',diagnostician =  '" + dtExamination.Rows[0][11] +
                //                        "',secondOpinion =  '" + dtExamination.Rows[0][12] +
                //                        "',pressure =  '" + dtExamination.Rows[0][13] +
                //                        "' WHERE examinationNo = '" + dtExamination.Rows[0][0] + "'";

                //sqlComm = new SqlCommand(strUpdateSQL, sqlConn);
                ////end delete 

                //add by jacky 20130420
                StringBuilder sbInsertSQL = new StringBuilder();

                //插入检查表(此时还没有检查编号)
                sbInsertSQL.Append("INSERT INTO examinationTB ");
                sbInsertSQL.Append("(examinationNo");
                sbInsertSQL.Append(",patientNo");
                sbInsertSQL.Append(",examinationDate");
                sbInsertSQL.Append(",stiffness");
                sbInsertSQL.Append(",iqr");
                sbInsertSQL.Append(",successRate");
                sbInsertSQL.Append(",aMode");
                sbInsertSQL.Append(",tmMode");
                sbInsertSQL.Append(",elasticGraph");
                sbInsertSQL.Append(",status");
                sbInsertSQL.Append(",detectorType");
                sbInsertSQL.Append(",submitDoctor");
                sbInsertSQL.Append(",diagnostician");
                sbInsertSQL.Append(",secondOpinion");
                sbInsertSQL.Append(",pressure");
                sbInsertSQL.Append(",flai");
                sbInsertSQL.Append(",flaiIQR)");
                sbInsertSQL.Append(" VALUES ");
                sbInsertSQL.Append("('").Append(dtExamination.Rows[0][0]).Append("'");
                sbInsertSQL.Append(",'").Append(dtExamination.Rows[0][1]).Append("'");
                sbInsertSQL.Append(",'").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("'");
                sbInsertSQL.Append(",'").Append(dtExamination.Rows[0][2]).Append("'");
                sbInsertSQL.Append(",'").Append(dtExamination.Rows[0][3]).Append("'");
                sbInsertSQL.Append(",'").Append(dtExamination.Rows[0][4]).Append("'");
                sbInsertSQL.Append(",@aMode");
                sbInsertSQL.Append(",@tmMode");
                sbInsertSQL.Append(",@elasticGraph");
                sbInsertSQL.Append(",'").Append(dtExamination.Rows[0][8]).Append("'");
                sbInsertSQL.Append(",'").Append(dtExamination.Rows[0][9]).Append("'");
                sbInsertSQL.Append(",'").Append(dtExamination.Rows[0][10]).Append("'");
                sbInsertSQL.Append(",'").Append(dtExamination.Rows[0][11]).Append("'");
                sbInsertSQL.Append(",'").Append(dtExamination.Rows[0][12]).Append("'");
                sbInsertSQL.Append(",'").Append(dtExamination.Rows[0][13]).Append("'");
                sbInsertSQL.Append(",'").Append(dtExamination.Rows[0][14]).Append("'");
                sbInsertSQL.Append(",'").Append(dtExamination.Rows[0][15]).Append("')");

                string strInsertSQL = sbInsertSQL.ToString();
                sqlComm = new SqlCommand(strInsertSQL, sqlConn);
                //end add

                parameter = new SqlParameter() { ParameterName = "@aMode", Value = dtExamination.Rows[0][5], SqlDbType = SqlDbType.Image };
                sqlComm.Parameters.Add(parameter);
                parameter = new SqlParameter() { ParameterName = "@tmMode", Value = dtExamination.Rows[0][6], SqlDbType = SqlDbType.Image };
                sqlComm.Parameters.Add(parameter);
                parameter = new SqlParameter() { ParameterName = "@elasticGraph", Value = dtExamination.Rows[0][7], SqlDbType = SqlDbType.Image };
                sqlComm.Parameters.Add(parameter);

                if (1 == sqlComm.ExecuteNonQuery())
                {//向检测表按条插入10条检测数据
                    if (dtStiffnessInfo.Rows.Count == 10)
                    {
                        string insertDetect = null;
                        foreach (DataRow dr in dtStiffnessInfo.Rows)
                        {
                            insertDetect = @"INSERT INTO detectTB (examinationNo, detectNo, stiffness, aMode, tmMode,elasticGraph,pressure,flai,remarks) VALUES (" +
                                          "'" + dr[0] + "'," + dr[1] + ",'" + dr[2] + "',@aMode,@tmMode,@elasticGraph,'" + dr[6] + "','" + dr[7] + "','" + dr[8] + "')";
                            sqlComm = new SqlCommand(insertDetect, sqlConn);

                            parameter = new SqlParameter() { ParameterName = "@aMode", Value = dr[3], SqlDbType = SqlDbType.Image };
                            sqlComm.Parameters.Add(parameter);
                            parameter = new SqlParameter() { ParameterName = "@tmMode", Value = dr[4], SqlDbType = SqlDbType.Image };
                            sqlComm.Parameters.Add(parameter);
                            parameter = new SqlParameter() { ParameterName = "@elasticGraph", Value = dr[5], SqlDbType = SqlDbType.Image };
                            sqlComm.Parameters.Add(parameter);

                            if (1 != sqlComm.ExecuteNonQuery())
                            {
                                //strMsg = "检测值插入失败"; 
                                result = false;
                                break;
                            }
                        }
                    }
                    else
                    {
                        //strMsg = "参数2出错";
                        result = false;

                    }

                    //strMsg = "检测数据建立成功";
                    result = true;

                }
                else
                {
                    //strMsg = "检查数据建立失败";
                    result = false;
                }
            }
            else
            {
                // strMsg = "参数1出错";
                result = false;
            }

            return result;
        }

        //更新报告中显示图片
        public bool UpdateReportImage(string strEexaminationNo, byte[] imgAmode, byte[] imgTMmode, byte[] imgElasticGraph)
        {
            bool result = false;
            DataSet ds = new DataSet();//ds
            if (ds != null)
            {
                ds.Clear();
            }

            if (sqlConn.State != ConnectionState.Open)
            {
                databaseOpen();
            }

            //add by jacky 20130420
            SqlParameter parameter;
            StringBuilder sbUpdateSQL = new StringBuilder();

            //插入检查表(此时还没有检查编号)
            sbUpdateSQL.Append("UPDATE examinationTB ");
            sbUpdateSQL.Append(" SET ");
            sbUpdateSQL.Append(" aMode = ");
            sbUpdateSQL.Append("@aMode");
            sbUpdateSQL.Append(",tmMode = ");
            sbUpdateSQL.Append("@tmMode");
            sbUpdateSQL.Append(",elasticGraph = ");
            sbUpdateSQL.Append("@elasticGraph");
            sbUpdateSQL.Append(" WHERE ");
            sbUpdateSQL.Append(" examinationNo = '");
            sbUpdateSQL.Append(strEexaminationNo);
            sbUpdateSQL.Append("'");
            string strUpdateSQL = sbUpdateSQL.ToString();

            sqlComm = new SqlCommand(strUpdateSQL, sqlConn);
            parameter = new SqlParameter() { ParameterName = "@aMode", Value = imgAmode, SqlDbType = SqlDbType.Image };
            sqlComm.Parameters.Add(parameter);
            parameter = new SqlParameter() { ParameterName = "@tmMode", Value = imgTMmode, SqlDbType = SqlDbType.Image };
            sqlComm.Parameters.Add(parameter);
            parameter = new SqlParameter() { ParameterName = "@elasticGraph", Value = imgElasticGraph, SqlDbType = SqlDbType.Image };
            sqlComm.Parameters.Add(parameter);

            if (1 == sqlComm.ExecuteNonQuery())
            {
                result = true;
            }
            else
            {
                result = false;
            }

            return result;

        }

        //更新诊断意见
        public bool UpdateSecondOpinion(string strEexaminationNo, string strSecondOpinion)
        {
            bool result = false;
            DataSet ds = new DataSet();//ds
            if (ds != null)
            {
                ds.Clear();
            }

            if (sqlConn.State != ConnectionState.Open)
            {
                databaseOpen();
            }
            string strUpdateSQL = "UPDATE examinationTB SET secondOpinion = '" + strSecondOpinion + "' WHERE (examinationNo = '" + strEexaminationNo + "')";
            sqlComm = new SqlCommand(strUpdateSQL, sqlConn);
            if (1 == sqlComm.ExecuteNonQuery())
            {
                result = true;
            }
            else
            {
                result = false;
            }

            return result;

        }

        //获取诊断意见
        public DataTable GetOpinionList()
        {
            DataSet ds = new DataSet();//ds
            if (ds != null)
            {
                ds.Clear();
            }

            if (sqlConn.State != ConnectionState.Open)
            {
                databaseOpen();
            }

            string strQuerySQL = "SELECT distinct secondOpinion from examinationTB";

            sqlDa.SelectCommand = new SqlCommand(strQuerySQL, sqlConn);
            sqlDa.Fill(ds, "OpinionList");
            return ds.Tables["OpinionList"];
        }

        //病例移除、恢复
        public bool ModifyPatientStatus(string strExaminationNo, string strStatus)
        {
            bool result = false;
            DataSet ds = new DataSet();//ds
            if (ds != null)
            {
                ds.Clear();
            }

            if (sqlConn.State != ConnectionState.Open)
            {
                databaseOpen();
            }
            string strUpdateSQL = "UPDATE examinationTB SET status  = '" + strStatus + "' WHERE (examinationNo = '" + strExaminationNo + "')";
            sqlComm = new SqlCommand(strUpdateSQL, sqlConn);
            if (1 == sqlComm.ExecuteNonQuery())
            {
                result = true;
            }
            else
            {
                result = false;
            }

            return result;
        }

        //检测报告数据
        public DataSet QueryReport(string strExaminationNo)
        {
            DataSet ds = new DataSet();
            if (ds != null)
            {
                ds.Clear();
            }

            if (sqlConn.State != ConnectionState.Open)
            {
                databaseOpen();
            }
            string strPatientSQL = "SELECT patientTB.patientNo, patientTB.patientName, patientTB.age, patientTB.gender, " +
                                   "patientTB.dept, patientTB.outpatientNo, patientTB.inpatientNo,patientTB.height,patientTB.weight," +
                                   "examinationTB.examinationNo,examinationTB.examinationDate, examinationTB.stiffness, examinationTB.iqr, " +
                                   "examinationTB.successRate,examinationTB.aMode, examinationTB.tmMode, " +
                                   "examinationTB.elasticGraph,examinationTB.submitDoctor," +
                                   "examinationTB.diagnostician, examinationTB.secondOpinion,examinationTB.pressure,examinationTB.flai, examinationTB.flaiIQR " +
                                   "FROM examinationTB INNER JOIN " +
                                   "patientTB ON examinationTB.patientNo = patientTB.patientNo " +
                                   "WHERE (examinationTB.examinationNo = '" + strExaminationNo + "')";

            string strDetectSQL = "SELECT examinationNo, detectNo, stiffness,pressure " +
                                  "FROM detectTB WHERE (examinationNo = '" + strExaminationNo + "')";

            sqlDa.SelectCommand = new SqlCommand(strPatientSQL, sqlConn);
            sqlDa.Fill(ds, "patientTB");

            sqlDa.SelectCommand = new SqlCommand(strDetectSQL, sqlConn);
            sqlDa.Fill(ds, "detectTB");

            return ds;
        }

        //更新医院信息
        public bool ModifyHospitalInfo(string strHospitalName, string strAddress, string strTelephone,
                                   string strFax, string strZipCode, string strWebsite, string strEmail)
        {
            DataSet ds = new DataSet();//ds
            if (ds != null)
            {
                ds.Clear();
            }

            if (sqlConn.State != ConnectionState.Open)
            {
                databaseOpen();
            }


            string strDelSQL = "DELETE FROM hospitalTB";
            sqlComm = new SqlCommand(strDelSQL, sqlConn);
            sqlComm.ExecuteNonQuery();

            string strUpdateSQL = "INSERT INTO hospitalTB(hospitalName,address,telephone,fax,zipCode,website,email) VALUES ('" +
                                    strHospitalName + "','" + strAddress + "','" + strTelephone + "','" + strFax + "','" +
                                    strZipCode + "','" + strWebsite + "','" + strEmail + "')";

            sqlComm = new SqlCommand(strUpdateSQL, sqlConn);
            if (1 == sqlComm.ExecuteNonQuery())
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        //读取医院信息
        public DataTable GetHospitalInfo()
        {
            DataSet ds = new DataSet();
            if (ds != null)
            {
                ds.Clear();
            }

            if (sqlConn.State != ConnectionState.Open)
            {
                databaseOpen();
            }
            string strQuerySQL = "SELECT hospitalName,address,telephone,fax,zipCode,website,email FROM hospitalTB";

            sqlDa.SelectCommand = new SqlCommand(strQuerySQL, sqlConn);
            sqlDa.Fill(ds, "hospitalInfoTB");

            if (ds.Tables["hospitalInfoTB"].Rows.Count > 0)
            {
                return ds.Tables["hospitalInfoTB"];
            }
            else
            {
                return null;
            }
        }

        //记录日志
        public void InsertLog(DateTime logTime, string strUserName, string strEvent)
        {
            DataSet ds = new DataSet();//ds
            if (ds != null)
            {
                ds.Clear();
            }

            if (sqlConn.State != ConnectionState.Open)
            {
                databaseOpen();
            }
            string strUpdateSQL = "INSERT INTO logTB (logTime, userName, event) VALUES ('" +
                                  logTime + "', '" + strUserName + "', '" + strEvent + "')";

            sqlComm = new SqlCommand(strUpdateSQL, sqlConn);
            if (1 == sqlComm.ExecuteNonQuery())
            {
            }
        }

        //查询日志
        public DataTable QueryLog(string strDate)
        {
            DataSet ds = new DataSet();//ds
            if (ds != null)
            {
                ds.Clear();
            }

            if (sqlConn.State != ConnectionState.Open)
            {
                databaseOpen();
            }

            DateTime inTime = Convert.ToDateTime(strDate).Date;

            string strQuerySQL = "SELECT logTime, userName, event FROM logTB " +
                                  "WHERE logTime BETWEEN '" + inTime.ToString("yyyy-MM-dd 00:00:00") +
                                  "' AND '" + inTime.ToString("yyyy-MM-dd 23:59:59") + "'";

            sqlDa.SelectCommand = new SqlCommand(strQuerySQL, sqlConn);
            sqlDa.Fill(ds, "logTB");
            if (ds.Tables["logTB"].Rows.Count > 0)
            {
                return ds.Tables["logTB"];
            }
            else
            {
                return null;
            }
        }

        //查询授权码
        public DataTable AuthorizeInfo()
        {
            DataSet ds = new DataSet();//ds
            if (ds != null)
            {
                ds.Clear();
            }

            if (sqlConn.State != ConnectionState.Open)
            {
                databaseOpen();
            }
            string strQuerySQL = "SELECT authorize, startDate FROM authorizeTB";

            sqlDa.SelectCommand = new SqlCommand(strQuerySQL, sqlConn);
            sqlDa.Fill(ds, "authorizeTempTB");

            if (ds.Tables["authorizeTempTB"].Rows.Count > 0)
            {
                return ds.Tables["authorizeTempTB"];
            }
            else
            {
                return null;
            }


        }

        //输入授权码
        public bool AuthorizeUpdate(string strAuthorize, DateTime startDT)
        {
            DataSet ds = new DataSet();
            bool result = false;

            if (ds != null)
            {
                ds.Clear();
            }

            if (sqlConn.State != ConnectionState.Open)
            {
                databaseOpen();
            }
            string strDelSQL = "DELETE FROM authorizeTB";
            string strUpdateSQL = " INSERT INTO authorizeTB(authorize ,startDate) VALUES ('" + strAuthorize + "' , '" + startDT + "')";
            sqlComm = new SqlCommand(strDelSQL, sqlConn);
            sqlComm.ExecuteNonQuery();

            sqlComm = new SqlCommand(strUpdateSQL, sqlConn);
            if (1 == sqlComm.ExecuteNonQuery())
            {
                result = true;
            }

            return result;

        }

        //查询数据库容量
        public DataTable checkDBCapacity()
        {
            DataSet ds = new DataSet();//ds
            if (ds != null)
            {
                ds.Clear();
            }

            if (sqlConn.State != ConnectionState.Open)
            {
                databaseOpen();
            }
            string strQuerySQL = "SELECT COUNT(*) AS rowsCount FROM examinationTB";

            sqlDa.SelectCommand = new SqlCommand(strQuerySQL, sqlConn);
            sqlDa.Fill(ds, "examinationTB");

            if (ds.Tables["examinationTB"].Rows.Count > 0)
            {
                return ds.Tables["examinationTB"];
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 查询硬盘容量
        /// </summary>
        /// <param name="driveDirectoryName"></param>
        /// <returns></returns>
        public DataRow CheckDiskCapacity(string driveDirectoryName)
        {

            DataTable deskDT = new DataTable();
            deskDT.Columns.Add(new DataColumn("totalFreeSpace", typeof(long)));
            deskDT.Columns.Add(new DataColumn("totalSize", typeof(long)));
            DataRow deskInfoDR = deskDT.NewRow();

            try
            {
                System.IO.DriveInfo drive = new System.IO.DriveInfo(driveDirectoryName);
                if (drive.IsReady)
                {
                    //modify by jacky 20130426 保留原有byte值
                    deskInfoDR["totalFreeSpace"] = (drive.TotalFreeSpace);
                    deskInfoDR["totalSize"] = (drive.TotalSize);
                    //end modify
                    deskDT.Rows.Add(deskInfoDR);
                }
                else
                {
                    deskInfoDR = null;
                }

                return deskInfoDR;
            }
            catch (Exception e)
            {
                return null;
            }
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="beforeDate"></param>
        /// <returns></returns>
        public bool DeleteDBForExport(string beforeDate)
        {
            //
            StringBuilder sbDeleteDetectTB = new StringBuilder();
            sbDeleteDetectTB.Append(" DELETE FROM detectTB ");
            sbDeleteDetectTB.Append(" WHERE detectTB.examinationNo IN");
            sbDeleteDetectTB.Append(" (SELECT examinationTB.examinationNo");
            sbDeleteDetectTB.Append(" FROM examinationTB");
            sbDeleteDetectTB.Append(" WHERE examinationTB.examinationDate < CONVERT (datetime,'");
            sbDeleteDetectTB.Append(Convert.ToDateTime(beforeDate).ToString("yyyy-MM-dd 23:59:59"));
            sbDeleteDetectTB.Append("'))");

            StringBuilder sbDeleteExaminationTB = new StringBuilder();
            sbDeleteExaminationTB.Append(" DELETE FROM examinationTB ");
            sbDeleteExaminationTB.Append(" WHERE examinationTB.examinationDate < CONVERT (datetime,'");
            sbDeleteExaminationTB.Append(Convert.ToDateTime(beforeDate).ToString("yyyy-MM-dd 23:59:59"));
            sbDeleteExaminationTB.Append("')");


            SqlTransaction sqlTransaction = null;

            try
            {
                //打开数据库连接
                if (sqlConn.State != ConnectionState.Open)
                {
                    databaseOpen();
                }

                //开启事务
                sqlTransaction = sqlConn.BeginTransaction();

                //创建command对象
                sqlComm = new SqlCommand();
                sqlComm.Connection = sqlConn;
                sqlComm.Transaction = sqlTransaction;

                //删除DetectTB
                sqlComm.CommandText = sbDeleteDetectTB.ToString();
                sqlComm.ExecuteNonQuery();

                //删除examinationTB
                sqlComm.CommandText = sbDeleteExaminationTB.ToString();
                sqlComm.ExecuteNonQuery();

                sqlComm = null;
                //提交事务
                sqlTransaction.Commit();
                //关闭数据库
                sqlConn.Close();

                return true;
            }
            catch (Exception ex)
            {
                if (sqlTransaction != null)
                {
                    //回滚事务
                    sqlTransaction.Rollback();

                }

                if (sqlConn != null)
                {
                    //关闭数据库
                    sqlConn.Close();
                }

                return false; 
            }

        }

        #region 删除数据库
        /// <summary>
        /// 
        /// </summary>
        /// <param name="strBackupPath"></param>
        /// <returns></returns>
        private bool DetachDB(string strBackupPath)
        {
            SqlDataAdapter sqlda;
            SqlConnection sqlDBFileConn = new SqlConnection("Data Source=.;User ID=sa;Password=etcd;Initial Catalog=master");
            DataTable dt = new DataTable();
            bool result = false;

            //数据复制不成功，分离数据库，删除移动存储设备上的数据库文件
            string cmdtxt = "SELECT spid FROM master..sysprocesses WHERE dbid=db_id('etcd_bak')" +
           "AND cmd != 'AWAITING COMMAND'" +
           "AND CMD != 'CHECKPOINT SLEEP'" +
           "AND CMD != 'LAZY WRITER]'" +
           "AND CMD != 'LOCK MONITOR'" +
           "AND CMD != 'SELECT'" +
           "AND CMD != 'SIGNAL HANDLER'";


            try
            {
                sqlda = new SqlDataAdapter(cmdtxt, sqlDBFileConn);
                sqlda.Fill(dt);
                sqlDBFileConn.Close();
                sqlDBFileConn.Dispose();

                //切换到master数据库，以方便杀死etcd_bak的进程
                sqlDBFileConn.ConnectionString = "Data Source=.;User ID=sa;Password=etcd;Initial Catalog=master";
                sqlComm.CommandType = CommandType.Text;
                sqlComm.Connection = sqlDBFileConn;
                sqlDBFileConn.Open();

                //强行关闭用户进程
                for (int i = 0; i <= dt.Rows.Count - 1; i++)
                {
                    if (Convert.ToInt32(dt.Rows[i][0]) > 50)
                    {
                        sqlComm.CommandText = " KILL " + dt.Rows[i][0].ToString();
                        sqlComm.ExecuteNonQuery();
                    }
                }

                //分离数据库
                sqlComm.CommandText = "EXEC master.dbo.sp_detach_db @dbname = 'etcd_bak', @keepfulltextindexfile=N'true'";

                bool delFile = true;
                int cycleFlag = -1;


                do
                {
                    cycleFlag += 1;
                    if (0 <= sqlComm.ExecuteNonQuery())
                    {
                        //删除删除移动存储设备上的数据库文件
                        try
                        {
                            StreamWriter sw = File.CreateText(strBackupPath);
                            try
                            {
                            }
                            finally
                            {
                                sw.Dispose();
                            }

                            File.Delete(strBackupPath);
                            delFile = false;
                        }
                        catch (System.Exception e)
                        {
                            delFile = true;
                        }
                    }
                    else
                    {
                        delFile = true;
                    }

                } while (delFile && cycleFlag <= 200);



                //备份数据库文件没有被删掉
                if (!delFile)
                {
                    result = true;
                }
            }
            catch
            {
                result = false;
            }
            finally
            {
                sqlDBFileConn.Close();
                sqlDBFileConn.Dispose();
            }
            return result;
        }
        #endregion

        #region 数据库导出
        /// <summary>
        /// 
        /// </summary>
        /// <param name="beforeDate"></param>
        /// <param name="strBackupPath"></param>
        /// <returns></returns>
        public bool ExportDB(string beforeDate, string strBackupPath)//strBackupPath为包含etcd_bak.mdf文件的全路径，如：f://etcd_bak.mdf
        {
            //检查传入的备份数据库文件路径，判断备份数据库文件是否存在
            bool exist = System.IO.File.Exists(strBackupPath);
            if (!exist)
            {
                return false;
            }

            SqlDataAdapter sqlda;
            DataTable dt = new DataTable();
            int clearFlag = 0;
            bool result = false;
            SqlConnection sqlDBFileConn = new SqlConnection("Data Source=.;AttachDbFilename=" + strBackupPath + ";" +
                                                              "Integrated Security=True;User Instance = false;Initial Catalog = etcd_bak;" +
                                                              "User ID=sa;Password=etcd;Pooling=false"); ;
            SqlConnection checkDBCon = new SqlConnection("server=.;database=master;uid=sa;PWD=etcd");
            SqlCommand sqlComm = new SqlCommand(); ;
            SqlCommand myCmd = new SqlCommand("select * from sys.databases where name='etcd_bak'", checkDBCon);
            //开始转移数据
            try
            {
                //检查服务器中是否挂载有备份数据库，如果有，直接删除。
                checkDBCon.Open();

                object n = myCmd.ExecuteScalar();

                if (n != null)
                {
                    myCmd.CommandText = "DROP DATABASE etcd_bak";
                    myCmd.ExecuteNonQuery();
                }
                checkDBCon.Close();


                //附加数据文件到数据库中       
                if (sqlConn.State != ConnectionState.Open)
                {
                    databaseOpen();
                }


                //开始转移数据
                sqlDBFileConn.Open();

                string strInsertExaminationTBSQL = "INSERT INTO etcd_bak.dbo.examinationTB SELECT * FROM etcd.dbo.examinationTB" +
                                                   " WHERE examinationDate <= convert(datetime,'" + Convert.ToDateTime(beforeDate).ToString("yyyy-MM-dd 23:59:59") + "')";


                string strInsertPatientTBSQL = "INSERT INTO etcd_bak.dbo.patientTB " +
                                               "SELECT patientTB.* FROM etcd.dbo.patientTB INNER JOIN " +
                                               "etcd.dbo.examinationTB ON patientTB.patientNo = examinationTB.patientNo " +
                                               "GROUP BY patientTB.patientNo, patientTB.patientName, patientTB.age,  " +
                                               "patientTB.creatDate, patientTB.gender, patientTB.dept, patientTB.outpatientNo,  " +
                                               "patientTB.inpatientNo, patientTB.telephone, patientTB.address,  " +
                                               "patientTB.casesSource, patientTB.userName, patientTB.remarks " +
                                               "HAVING (patientTB.patientNo IN " +
                                               "(SELECT patientNo FROM etcd.dbo.examinationTB " +
                                               "WHERE (examinationDate <= CONVERT(datetime, '" + Convert.ToDateTime(beforeDate).ToString("yyyy-MM-dd 23:59:59") + "')) " +
                                               "GROUP BY patientNo))";

                string strInsterDetectTBSQL = "INSERT INTO etcd_bak.dbo.detectTB SELECT detectTB.serialNo, detectTB.examinationNo, detectTB.detectNo, " +
                                              "detectTB.stiffness,detectTB.aMode, detectTB.tmMode, detectTB.pressure, detectTB.remarks  " +
                                              "FROM etcd.dbo.detectTB INNER JOIN etcd.dbo.examinationTB  " +
                                              "ON detectTB.examinationNo = examinationTB.examinationNo " +
                                              "where detectTB.examinationNo IN ( " +
                                              "SELECT examinationNo FROM etcd.dbo.examinationTB  " +
                                              "WHERE (examinationDate <= CONVERT(datetime, '" + Convert.ToDateTime(beforeDate).ToString("yyyy-MM-dd 23:59:59") + "')))";


                //sqlComm = new SqlCommand("SET IDENTITY_INSERT etcd_bak.dbo.examinationTB ON",sqlDBFileConn);
                //sqlComm.ExecuteNonQuery();

                sqlComm = new SqlCommand(strInsterDetectTBSQL, sqlDBFileConn);
                int detectRows = sqlComm.ExecuteNonQuery();
                //////数据插入成功标识                
                ////if (detectRows >= 0)
                ////{
                ////    exportFlag += 10;
                ////}

                sqlComm = new SqlCommand(strInsertExaminationTBSQL, sqlDBFileConn);
                int examinationRows = sqlComm.ExecuteNonQuery();
                //////数据插入成功标识                
                ////if (examinationRows >= 0)
                ////{
                ////    exportFlag += 20;
                ////}

                sqlComm = new SqlCommand(strInsertPatientTBSQL, sqlDBFileConn);
                int patientRows = sqlComm.ExecuteNonQuery();
                //////数据插入成功标识                
                ////if (patientRows >= 0)
                ////{
                ////    exportFlag += 40;
                ////}

                if (0 <= examinationRows && 0 <= patientRows && 0 <= detectRows)
                {
                    //数据复制成功，分离数据库
                    string cmdtxt = "SELECT spid FROM master..sysprocesses WHERE dbid=db_id('etcd_bak')" +
                       "AND cmd != 'AWAITING COMMAND'" +
                       "AND CMD != 'CHECKPOINT SLEEP'" +
                       "AND CMD != 'LAZY WRITER]'" +
                       "AND CMD != 'LOCK MONITOR'" +
                       "AND CMD != 'SELECT'" +
                       "AND CMD != 'SIGNAL HANDLER'";

                    sqlda = new SqlDataAdapter(cmdtxt, sqlDBFileConn);
                    sqlda.Fill(dt);
                    sqlDBFileConn.Close();
                    sqlDBFileConn.Dispose();

                    //切换到master数据库，以方便杀死etcd_bak的进程
                    sqlDBFileConn.ConnectionString = "Data Source=.;User ID=sa;Password=etcd;Initial Catalog=master";
                    sqlComm.CommandType = CommandType.Text;
                    sqlComm.Connection = sqlDBFileConn;
                    sqlDBFileConn.Open();

                    //强行关闭用户进程
                    for (int i = 0; i <= dt.Rows.Count - 1; i++)
                    {
                        if (Convert.ToInt32(dt.Rows[i][0]) > 50)
                        {
                            sqlComm.CommandText = " KILL " + dt.Rows[i][0].ToString();
                            sqlComm.ExecuteNonQuery();
                        }
                    }

                    //分离数据库
                    sqlComm.CommandText = "EXEC master.dbo.sp_detach_db @dbname = 'etcd_bak', @keepfulltextindexfile=N'true'";
                    sqlComm.ExecuteNonQuery();

                    //分离外部存储设备的数据库成功，删除本地数据库中已经被复制的数据 
                    //删除本地数据库检测表中已经导出的数据
                    cmdtxt = "DELETE FROM etcd.dbo.detectTB " +
                            "WHERE detectTB.examinationNo IN( " +
                            "SELECT detectTB.examinationNo FROM etcd.dbo.detectTB INNER JOIN etcd.dbo.examinationTB   " +
                            "ON detectTB.examinationNo = examinationTB.examinationNo  " +
                            "where detectTB.examinationNo IN (  " +
                            "SELECT examinationNo FROM etcd.dbo.examinationTB " +
                            "WHERE (examinationDate <= CONVERT(datetime, '" + Convert.ToDateTime(beforeDate).ToString("yyyy-MM-dd 23:59:59") + "')))) ";
                    sqlComm = new SqlCommand(cmdtxt, sqlConn);
                    if (detectRows == sqlComm.ExecuteNonQuery())
                    {
                        //数据清理成功标识
                        detectRows = 1;
                    }
                    else
                    {
                        //数据清理不成功
                        detectRows = 0;
                    }

                    //删除本地数据库检查表中已经导出的数据
                    cmdtxt = "DELETE FROM etcd.dbo.examinationTB WHERE examinationDate <= convert(datetime,'" +
                             Convert.ToDateTime(beforeDate).ToString("yyyy-MM-dd 23:59:59") + "')";
                    sqlComm = new SqlCommand(cmdtxt, sqlConn);
                    if (examinationRows == sqlComm.ExecuteNonQuery())
                    {
                        //数据清理成功标识
                        examinationRows = 2;
                    }
                    else
                    {
                        //数据清理不成功
                        examinationRows = 0;
                    }

                    //删除数据库中的已经导出的患者，但如果患者在导出日期后活动过的则不删除
                    cmdtxt = "DELETE FROM etcd.dbo.patientTB " +
                            "WHERE patientNo in (SELECT patientNo FROM patientTB  " +
                            "WHERE ((creatDate <= CONVERT(datetime, '2011-01-01')) " +
                            "AND patientNo not in (SELECT patientNo FROM examinationTB	 " +
                            "WHERE((examinationDate > CONVERT(datetime, N'2010-1-1')) " +
                            "AND (patientNo IN (SELECT patientNo FROM [etcd].[dbo].[examinationTB] " +
                            "WHERE (examinationDate <= CONVERT(datetime, N'2010-1-1')) " +
                            "GROUP BY patientNo))) GROUP BY patientNo)) GROUP BY patientTB.patientNo)";

                    sqlComm = new SqlCommand(cmdtxt, sqlConn);
                    if (0 <= sqlComm.ExecuteNonQuery())
                    {
                        //数据清理成功标识，但删除的患者不一定和导出数据时的那么多。因此这里可能进不来！！！
                        patientRows = 4;
                    }
                    else
                    {
                        //数据清理不成功
                        patientRows = 0;
                    }

                    clearFlag = detectRows + examinationRows + patientRows;

                    if (clearFlag == 7)
                    {
                        result = true;
                    }
                    else
                    {
                        //本机数据库中刚刚被导出的数据清理不完全
                        result = false;
                    }
                }
                else
                {

                    //导出数据不完全，删除移动设备中的数据文件
                    DetachDB(strBackupPath);
                    result = false;
                }
            }
            catch (SqlException ex)
            {
                result = false;
                ////数据库操作发生错误
                //clearFlag = -2;
            }
            catch (Exception ex)
            {
                result = false;
                ////其它错误
                //clearFlag = -3;
            }
            finally
            {
                sqlConn.Close();
                sqlConn.Dispose();
                sqlDBFileConn.Close();
                sqlDBFileConn.Dispose();
            }

            return result;
        }
        #endregion

        /// <summary>
        /// 验证地址字符串是否包含有除下划线以外的符号   xj 20120914
        /// </summary>
        /// <param name="textContext"></param>
        /// <returns></returns>
        public bool IsStrFormat(string textContext)
        {
            string pattern = @"\w*";
            return Regex.IsMatch(textContext, pattern);   // 匹配正则表达式
        }

        /// <summary>
        /// 验证电子邮箱地址字符串是否符合规范   xj 20120914
        /// </summary>
        /// <param name="textContext"></param>
        /// <returns></returns>
        public bool IsEmail(string textContext)
        {
            string pattern = @"\w+@\w+(\.\w+)*";
            return Regex.IsMatch(textContext, pattern);   // 匹配正则表达式
        }

        /// <summary>
        /// 验证电话号码字符串是否符合规范   xj 20120914
        /// </summary>
        /// <param name="textContext"></param>
        /// <returns></returns>
        public bool IsPhone(string textContext)
        {
            string pattern = @"0?13\d{9}|0?15\d{9}|0?18\d{9}|0[1-9][0-9]-[1-9]\d{6,7}|0[1-9][1-9][0-9]-[1-9]\d{6,7}|0[1-9][1-9][0-9][1-9]\d{6,7}|0[1-9][0-9][1-9]\d{6,7}|[1-9]\d{6,7}";
            Match mh = Regex.Match(textContext, pattern);   // 匹配正则表达式

            if (0 == String.Compare(mh.ToString(), textContext))
            {
                return true;
            }
            else
            {
                return false;
            }          
        }

        /// <summary>
        /// 验证传真号码字符串是否符合规范   xj 20120914
        /// </summary>
        /// <param name="textContext"></param>
        /// <returns></returns>
        public bool IsFax(string textContext)
        {
            string pattern = @"0[1-9][0-9]-[1-9]\d{6,7}|0[1-9][1-9][0-9]-[1-9]\d{6,7}|0[1-9][1-9][0-9][1-9]\d{6,7}|0[1-9][0-9][1-9]\d{6,7}|[1-9]\d{6,7}";
            Match mh = Regex.Match(textContext, pattern);   // 匹配正则表达式

            if (0 == String.Compare(mh.ToString(), textContext))
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 验证住院号，门诊号字符串是否符合规范   xj 20120914
        /// </summary>
        /// <param name="textContext"></param>
        /// <returns></returns>
        public bool IsPatientNo(string textContext)
        {
            string pattern = @"[a-z0-9A-Z]{0,4}\d{4,12}";      // 匹配正则表达式

            Match mh = Regex.Match(textContext, pattern);
            if (0 == String.Compare(mh.ToString(), textContext))
            {
                return true;   
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 验证邮编格式是否正确   xj 20120914
        /// </summary>
        /// <param name="textContext"></param>
        /// <returns></returns>
        public bool IsZipCode(string textContext)
        {
            string pattern = @"\d{6}";
            return Regex.IsMatch(textContext, pattern);   // 匹配正则表达式
        }
     
        /// <summary>
        /// 验证网址格式是否正确   xj 20120914
        /// </summary>
        /// <param name="textContext"></param>
        /// <returns></returns>
        public bool IsWebsite(string textContext)
        {
            string pattern = @"((http://)?([\w]+\.)+[\w]+(/[\w+./?%&=]*)?)?";
            return Regex.IsMatch(textContext, pattern);   // 匹配正则表达式
        }

    }
}
